#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriverPlugin>
#include <QPluginLoader>
#include <QDebug>
#include <QMetaObject>
#include <QMetaProperty>

#include "usecasedbadapter.h"

UseCaseDBAdapter::UseCaseDBAdapter()
{
    m_itemTypeHash.insert("ARSExecActl", 0);
    m_itemTypeHash.insert("ARSExecFltr", 1);
    m_itemTypeHash.insert("ARSExecRootItem", 2);
    m_itemTypeHash.insert("ARSExecActlBlock", 3);
    m_itemTypeHash.insert("ARSExecFltrBlock", 4);
    m_itemTypeHash.insert("ARSExecGuide", 5);
    m_itemTypeHash.insert("ARSExecActionCallGuide", 6);
    m_itemTypeHash.insert("ARSExecActionChangeFields", 7);
    m_itemTypeHash.insert("ARSExecActionCloseWindow", 8);
    m_itemTypeHash.insert("ARSExecActionCommitChanges", 9);
    m_itemTypeHash.insert("ARSExecActionDDE", 10);
    m_itemTypeHash.insert("ARSExecActionDirectSQL", 11);
    m_itemTypeHash.insert("ARSExecActionDSOAction", 12);
    m_itemTypeHash.insert("ARSExecActionExitGuide", 13);
    m_itemTypeHash.insert("ARSExecActionGoToGuideLabel", 14);
    m_itemTypeHash.insert("ARSExecActionGoto", 15);
    m_itemTypeHash.insert("ARSExecActionLogToFile", 16);
    m_itemTypeHash.insert("ARSExecActionMessage", 17);
    m_itemTypeHash.insert("ARSExecActionNotify", 18);
    m_itemTypeHash.insert("ARSExecActionOLEAutomation", 19);
    m_itemTypeHash.insert("ARSExecActionOpenWindow", 20);
    m_itemTypeHash.insert("ARSExecActionRunMacro", 21);
    m_itemTypeHash.insert("ARSExecActionRunProcess", 22);
    m_itemTypeHash.insert("ARSExecActionWait", 23);
    m_itemTypeHash.insert("ARSExecActionPushFields", 24);
    m_itemTypeHash.insert("ARSExecActionSetFields", 25);
    m_itemTypeHash.insert("ARSExecActionService", 26);
    m_itemTypeHash.insert("ARSExecFieldAssign", 27);
    m_itemTypeHash.insert("ARSExecSQLLine", 28);
    m_itemTypeHash.insert("ARSExecAPILine", 29);
}

QString UseCaseDBAdapter::name()const
{
    return "usecasedbadapter";
}

QString UseCaseDBAdapter::description()const
{
    return "This adapter provides communication with workflow database";
}

bool UseCaseDBAdapter::connectToDatabase()
{
    QPluginLoader loader("c:\\QtSDK\\Desktop\\Qt\\4.7.3\\mingw\\plugins\\sqldrivers\\qsqlocid4.dll");
    QObject *plugin = loader.instance();

    if (!plugin)
        return false;

    QSqlDriverPlugin *sqlPlugin  = qobject_cast<QSqlDriverPlugin *>(plugin);

    if (!sqlPlugin ) {
        qDebug() << "SQL plugin loading failed";
        return false;
    }

    QSqlDatabase db = QSqlDatabase::addDatabase(sqlPlugin->create("QOCI"));
    db.setHostName("10.151.135.201");
    db.setPort(51521);
    db.setDatabaseName("EWMS2");
    if(!db.open("aradmin", "ar#admin#"))
    {
        qDebug("Cannot connect to database. ");
        qDebug() << db.lastError().text();
        return false;
    }

    return true;
}

QStringList UseCaseDBAdapter::loadModelList()
{
    QSqlQuery query("SELECT model_name FROM bb_model");

    QStringList resultList;
    while(query.next())
        resultList.append(query.value(0).toString());

    return resultList;
}

bool UseCaseDBAdapter::addModel(const QString & name, DynamicModel * model)
{
    int modelID = addModelHead(name, QString());
    if(modelID == -1)
    {
        qDebug("Cannot create model.");
        return false;
    }

    addModelItem(modelID, model->rootItem()->child(0), -1);

    return true;
}

DynamicModel * UseCaseDBAdapter::loadModel(int modelID)
{
    QSqlQuery query;
    query.prepare("SELECT id, itemID, itemType, parent "\
                  "FROM vARSExecModelItem "\
                  "WHERE modelid = ? "\
                  "ORDER BY id");
    query.addBindValue(modelID);

    if(!query.exec())
    {
        qDebug(qPrintable("Cannot retrieve model. " + query.lastError().text()));
        return 0;
    }

    QHash <int, ARSExecItem *> itemIDMap;
    ARSExecModelRootItem * root = new ARSExecModelRootItem();
    while(query.next())
    {
        int id = query.value(0).toInt();
        QString type = query.value(2).toString();
        ARSExecItem * parentItem = 0;
        if(!query.value(3).isNull())
            parentItem = itemIDMap.value(query.value(3).toInt());
        else
            parentItem = root;

        ARSExecItem * item = ARSExecItem::createItemByName(type, parentItem);
        itemIDMap.insert(id, item);
    }

    DynamicModel * model = new DynamicModel(root);
    return model;
}

int UseCaseDBAdapter::addModelHead(const QString name, const QString & description)
{
    QSqlQuery query;
    query.prepare("INSERT INTO bb_model (\"model_name\", \"description\") VALUES (?, ?)");
    query.addBindValue(name);
    query.addBindValue(description);
    if(!query.exec())
    {
        qDebug(qPrintable("Cannot create model in database. " + query.lastError().text()));
        return -1;
    }

    query.prepare("SELECT bb_model_seq.currval FROM DUAL");
    if(!query.exec())
    {
        qDebug(qPrintable("Cannot create model in database. " + query.lastError().text()));
        return -1;
    }
    query.first();
    return query.value(0).toInt();
}

bool UseCaseDBAdapter::addModelItem(int modelID, ARSExecItem * item, int parentID)
{
    QSqlQuery query;
    int headID;

    headID = addModelItemHead(item, item->metaObject());

    query.prepare("INSERT INTO bb_modelitem(\"modelid\", \"itemid\", \"itemtype\", \"parent\", \"row\") VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(modelID);
    query.addBindValue(headID);
    query.addBindValue(m_itemTypeHash.value(item->metaObject()->className()));
    if(parentID == -1)
        query.addBindValue(QVariant());
    else
        query.addBindValue(parentID);
    query.addBindValue(item->row());

    if(!query.exec())
    {
        qDebug(qPrintable("Cannot insert item. " + query.lastError().text()));
        qDebug() << query.lastQuery();
        return false;
    }

    query.prepare("SELECT bb_modelitem_seq.currval FROM DUAL");
    if(!query.exec())
    {
        qDebug(qPrintable("Cannot create model in database. " + query.lastError().text()));
        return -1;
    }

    query.first();
    int itemID = query.value(0).toInt();
    for(int i = 0; i < item->childCount(); i++)
        addModelItem(modelID, item->child(i), itemID);

    return true;
}

int UseCaseDBAdapter::addModelItemHead(ARSExecItem * item, const QMetaObject * itemMetaObject)
{
    QSqlQuery query;
    QString itemTableName = QString::fromAscii(itemMetaObject->className());
    itemTableName.remove("ARSExec");
    QString queryHead = "INSERT INTO bb_" + itemTableName;
    QStringList fieldsList;
    QStringList valuesList;

    if(QString::fromAscii(itemMetaObject->className()) != QString("ARSExecItem"))
    {
        int parentID;

        fieldsList.append("parentid");
        parentID = addModelItemHead(item, itemMetaObject->superClass());
        valuesList.append(QString::number(parentID));
    }

    for(int i = itemMetaObject->propertyOffset(); i < itemMetaObject->propertyCount(); i++)
    {
        const QMetaProperty metaProperty = itemMetaObject->property(i);
        if(!metaProperty.isWritable())
            continue;

        if(QString(metaProperty.name()) == QString("objectsType"))
            continue;

        QVariant propertyValue = item->property(metaProperty.name());

        ARSDummyObject * dummyObject;
        if(metaProperty.typeName() == QString("ARSDummyActiveLink*"))
        {
            dummyObject = propertyValue.value<ARSDummyActiveLink *>();
            if(dummyObject)
            {
                fieldsList.append(QString::fromAscii(metaProperty.name()));
                valuesList.append("'" + dummyObject->name() + "'");
            }
        }
        else
            if(metaProperty.typeName() == QString("ARSDummyFilter*"))
            {
                dummyObject = propertyValue.value<ARSDummyFilter *>();
                if(dummyObject)
                {
                    fieldsList.append(QString::fromAscii(metaProperty.name()));
                    valuesList.append("'" + dummyObject->name() + "'");
                }
            }
            else
                if(metaProperty.typeName() == QString("ARSDummyForm*"))
                {
                    dummyObject = propertyValue.value<ARSDummyForm *>();
                    if(dummyObject)
                    {
                        fieldsList.append(QString::fromAscii(metaProperty.name()));
                        valuesList.append("'" + dummyObject->name() + "'");
                    }
                }
                else
                    if(metaProperty.typeName() == QString("ARSDummyField*"))
                    {
                        ARSDummyField * field = propertyValue.value<ARSDummyField *>();
                        if(field)
                        {
                            fieldsList.append(QString::fromAscii(metaProperty.name()) + "ID");
                            valuesList.append(QString::number(field->id()));
                            fieldsList.append(QString::fromAscii(metaProperty.name()) + "Form");
                            valuesList.append("'" + field->parentForm()->name() + "'");
                        }
                    }
                    else
                        if(metaProperty.typeName() == QString("ARSDummyGuide*"))
                        {
                            ARSDummyGuide * guide = propertyValue.value<ARSDummyGuide *>();
                            if(guide)
                            {
                                fieldsList.append(QString::fromAscii(metaProperty.name()) + "Name");
                                valuesList.append("'" + guide->name() + "'");
                                fieldsList.append(QString::fromAscii(metaProperty.name()) + "Type");
                                valuesList.append("'" + QString::number(guide->objectsType()) + "'");
                            }
                        }
                        else
                        {
                            fieldsList.append(QString::fromAscii(metaProperty.name()));
                            valuesList.append(simpleVariant2String(propertyValue));
                        }
        }

//    if(QString::fromAscii(itemMetaObject->className()) == QString("ARSExecItem"))
//    {
//        int index = fieldsList.indexOf("type");

//        fieldsList.removeAt(index);
//        valuesList.removeAt(index);
//    }

//    if(QString::fromAscii(itemMetaObject->className()) == QString("ARSExecAction"))
//    {
//        int index = fieldsList.indexOf("actionType");

//        fieldsList.removeAt(index);
//        valuesList.removeAt(index);
//    }

    for(int i = 0; i < fieldsList.count(); i++)
    {
        fieldsList.replace(i, "\"" + fieldsList.at(i).toLower() + "\"");
    }

    QString queryString = "SELECT \"id\" FROM bb_" + itemTableName + " WHERE ";
    for(int i = 0; i < fieldsList.count(); i++)
    {
        queryString += fieldsList.at(i) + "=" + valuesList.at(i);
        if(i < fieldsList.count() - 1)
            queryString += " AND ";
    }

    query.prepare(queryString);
    if(!query.exec())
    {
        qDebug(qPrintable("Cannot select item. " + query.lastError().text()));
        qDebug() << queryString;
        return false;
    }

    query.first();
    if(query.isValid())
        return query.value(0).toInt();

    queryString = queryHead + "(" + fieldsList.join(",") + ") VALUES (" + valuesList.join(",") + ")";
    query.prepare(queryString);

    if(!query.exec())
    {
        qDebug(qPrintable("Cannot insert item. " + query.lastError().text()));
        qDebug() << queryString;
        return false;
    }

    query.prepare("SELECT bb_item_seq.currval FROM DUAL");
    if(!query.exec())
    {
        qDebug(qPrintable("Cannot create model in database. " + query.lastError().text()));
        return -1;
    }

    query.first();
    return query.value(0).toInt();
}

QString UseCaseDBAdapter::simpleVariant2String(QVariant value)
{
    if(value.isNull())
        return "NULL";

    if(value.type() == QVariant::String || value.type() == QVariant::Time)
        return "'" + value.toString() + "'";

    if(value.type() == QVariant::DateTime)
    {
        QDateTime dateTime = value.toDateTime();
        return "TIMESTAMP '" + dateTime.toString("yyyy-MM-dd hh:mm:ss") + "'";
    }

    if(!value.canConvert(QVariant::String))
        qDebug() << "don't know how to convert \"" << value.typeName() << "\"";

    return value.toString();
}
